/*
 *  This file is part of Spidex.
 *  
 *  Spidex is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *  
 *  Spidex is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *  
 *  You should have received a copy of the GNU General Public License
 *  along with Spidex.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * This class is implementation of DBManager and works with database SQLite.
 * Class can create all tables with triggers and also create idsum function for
 * increment SERIAL ID variable type (like postres). Class also provide function
 * for creation records, selection and deletion.
 * 
 * Database works only in single mode,no threads or other processes are allowed.
 * 
 */

package spidex.dbmanager;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import org.sqlite.Function;
import spidex.DVDInfo;

/**
 *
 * @author xkucer11
 */
public class DBSQLite implements DBManager {
    private Connection connection;
    //Prepared statements for faster DB work
    private PreparedStatement insertFilmStmt;
    private PreparedStatement updateFilmStmt;
    private PreparedStatement updateAdditionalInfoStmt;
    private PreparedStatement insertActorStmt;
    private PreparedStatement insertDirectorStmt;
    private PreparedStatement insertFilm2ActorStmt;
    private PreparedStatement insertFilm2DirectorStmt;
    private PreparedStatement insertAdditionalInfoStmt;
    private PreparedStatement findFilmIdStmt;
    private PreparedStatement findFilmStmt;
    private PreparedStatement findDirectorStmt;
    private PreparedStatement findActorStmt;
    private PreparedStatement findInfoResult;
    private PreparedStatement findActorResult;
    private PreparedStatement findDirectorResult;
    private PreparedStatement existFilmStmt;
    
    private Logger logger = Logger.getLogger("Spidex");


    
    /**
     * Function create all tables and triggers if neccessary, also prepare 
     * statements and functions for database handling
     * @param dbUrl string or URL of database
     * @throws java.lang.ClassNotFoundException when sqlite module isn't include
     * @throws java.sql.SQLException when any database error occurs
     */
    public void  openDb(String dbUrl)
            throws ClassNotFoundException, SQLException {
        DatabaseMetaData dbmd;
        ResultSet result;

        if(dbUrl == null) {
            throw new NullPointerException("dbUrl");
        }

        Class.forName("org.sqlite.JDBC");
        this.connection = DriverManager.getConnection("jdbc:sqlite:"+dbUrl);
        if( connection.isReadOnly() ) {
            logger.log(Level.WARNING, "Database is read-only");
            throw new SQLException("Database is read-only");
        }
        
        dbmd = connection.getMetaData();
        result = dbmd.getTables(null, null, "film", null);
        if(! result.next() ) {
            result.close();
            logger.log(Level.INFO,"Database doesn't contains records table");
            this.createTables();

        }
        result.close();
        this.createStatements();

    }
    
        public void createTables() throws SQLException {
        
        Statement statement = this.connection.createStatement();
        statement.execute( 
            "CREATE TABLE film (" +
                " ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                " name VARCHAR(40) NOT NULL," +
                " year INT," +
                " rating INT,"+
                " length INT,"+
                " genre VARCHAR(40)," +
                " posterURL TEXT" +
            " )"
        );
        statement.execute( 
            "CREATE TABLE actors (" +
                " ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " name VARCHAR(40) NOT NULL" +
            " )"
        );

        statement.execute(
            "CREATE TABLE directors (" +
                " ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " name VARCHAR(40) NOT NULL" +
            " )"
        );
        statement.execute( 
            "CREATE TABLE film2directors (" +
                " ID_film INT NOT NULL, " +
                " ID_director NOT NULL" +
            " )"
        );
        
        statement.execute( 
            "CREATE TABLE film2actors (" +
                " ID_film INT NOT NULL, " +
                " ID_actor INT NOT NULL" +
            " )"
        );
        
        statement.execute( 
            "CREATE TABLE filmInfo (" +
                " ID_film INT NOT NULL," +
                " type INT NOT NULL," +
                " compilation VARCHAR(40)," +
                " lendTo VARCHAR(40)," +
                " borrowedFrom VARCHAR(40)," +
                " info VARCHAR(180)" +
            " )"
        );

        statement.execute(
            "CREATE TRIGGER del_film2directors DELETE ON film\n" +
            "BEGIN\n" +
                "DELETE FROM film2directors WHERE id_film=old.id;\n"+
            "END;"
        );
        
        statement.execute(
            "CREATE TRIGGER de_film2actors DELETE ON film\n" +
            "BEGIN\n" +
                "DELETE FROM film2actors WHERE id_film=old.id;\n"+
            "END;"
        );

        statement.close();
    }
    
    /**
     * Function prepares all necessary functions and statements
     * @throws java.sql.SQLException when error in database occurs
     */
    private void createStatements() throws SQLException {
        
        Function.create(this.connection, "idmax", new Function.Aggregate() {
            private int sum = 0;
            @SuppressWarnings("empty-statement")
                protected void xStep() throws SQLException {
                    if(sum < value_int(0) ) {
                        sum = value_int(0);
                    };
                }
                protected void xFinal() throws SQLException {
                    result(sum);
                }
            }
        );
        
        this.insertFilmStmt = this.connection.prepareStatement(
            "INSERT INTO film VALUES ("+
                "NULL,?,?,?,?,?,?)"
        );
        
        this.updateFilmStmt = this.connection.prepareStatement(
            "UPDATE film SET name=?, year=?, rating=?, length=?, genre=? " +
                "WHERE id=?"
        );
        
        this.updateAdditionalInfoStmt = this.connection.prepareStatement(
            "UPDATE filmInfo SET type=?, compilation=?, lendTo=?," +
                "borrowedFrom=?, info=? WHERE ID_film=?"
        );
        
        this.insertActorStmt = this.connection.prepareStatement(
            "INSERT INTO actors VALUES ("+
                "NULL,?)"
        );
        
        this.insertDirectorStmt = this.connection.prepareStatement(
            "INSERT INTO directors VALUES ("+
                "NULL,?)"
        );
        
        this.insertAdditionalInfoStmt = this.connection.prepareStatement(
            "INSERT INTO filmInfo VALUES ("+
                "?,?,?,?,?,?)"
        );
        
        this.findFilmIdStmt = this.connection.prepareStatement(
            "SELECT max(id) FROM film WHERE name=?"
        );
        
        this.findFilmStmt = this.connection.prepareStatement(
            "SELECT * FROM film WHERE name LIKE ?"
        );
        
        this.findInfoResult = this.connection.prepareStatement(
            "SELECT type, compilation, lendTo, borrowedFrom, info"+
                " FROM filmInfo WHERE ID_film=?"
        );
        
        this.findActorResult =  this.connection.prepareStatement(
            "SELECT name from actors WHERE actors.id IN ("+
                    "SELECT id_actor FROM film2actors WHERE id_film=?)"
        );
        
        this.findDirectorResult =  this.connection.prepareStatement(
                "SELECT name from directors WHERE directors.id IN ("+
                    "SELECT id_director FROM film2directors WHERE id_film=?)"
                    );
        
        this.findActorStmt = this.connection.prepareStatement(
            "SELECT id FROM actors WHERE name=?"
        );
        
        this.findDirectorStmt = this.connection.prepareStatement(
            "SELECT id FROM directors WHERE name=?"
        );
        
        this.insertFilm2ActorStmt = this.connection.prepareStatement(
            "INSERT INTO film2actors VALUES (?,?)"
        );

        this.insertFilm2DirectorStmt = this.connection.prepareStatement(
            "INSERT INTO film2directors VALUES (?,?)"
        );

        this.existFilmStmt = this.connection.prepareStatement(
            "SELECT id FROM film WHERE id=?"
        );
    }
    
    /**
     * Function insert all included data to database
     * @param info class with all data about DVD 
     * @throws java.sql.SQLException when any database error occurs
     */
    public int insertRecord(DVDInfo info) throws SQLException {
        int id;
//        TODO: erase
//        System.out.println("Inserting: "+info);
        this.insertFilmStmt.setString(1, info.getTitle());
        this.insertFilmStmt.setInt(2, info.getYearOfRelease());
        this.insertFilmStmt.setInt(3, info.getRating() );
        this.insertFilmStmt.setInt(4, info.getLength());
        this.insertFilmStmt.setString(5, info.getGenre());
        this.insertFilmStmt.setString(6, info.getPosterUrl());
        this.insertFilmStmt.execute();
        id = this.getFilmID(info.getTitle());
        info.setId(id);
        this.insertActors(id, info);
        this.insertDirectors(id, info);
        this.insertAdditionalInfo(id, info);
        return id;
    }
    
    public void updateRecord(DVDInfo info) throws SQLException {
        int id = info.getId();
        if(id == 0) {
            return;
        }
//        TODO 
//        System.out.println("----------------------------");
//        System.out.println(info);
        //"UPDATE film SET name=?, year=?, rating=?, length=? genre=? WHERE id=?"
        this.updateFilmStmt.setString(1, info.getTitle());
        this.updateFilmStmt.setInt(2, info.getYearOfRelease());
        this.updateFilmStmt.setInt(3, info.getRating());
        this.updateFilmStmt.setInt(4, info.getLength());
        this.updateFilmStmt.setString(5, info.getGenre());
        this.updateFilmStmt.setInt(6, id);
        this.updateFilmStmt.execute();   
        
        //"UPDATE filmInfo SET type=?, compilation=?, lendTo=?,
        //borrowedFrom=?, lentTo=? WHERE id_film=?"
        this.updateAdditionalInfoStmt.setInt(1, info.getType().getInt());
        this.updateAdditionalInfoStmt.setString(2, info.getCompilation());
        this.updateAdditionalInfoStmt.setString(3, info.getRentedToWho());
        this.updateAdditionalInfoStmt.setString(4, info.getBorrowedFromWho());
        this.updateAdditionalInfoStmt.setString(5, info.getInfo());
        this.updateAdditionalInfoStmt.setInt(6, info.getId());
        this.updateAdditionalInfoStmt.execute();
        
        this.insertActors(id, info);
        this.insertDirectors(id, info);
        this.insertAdditionalInfo(id, info);
    }

    /**
     * Function insert new directors to database and create connection to film
     * @param filmId unique ID of new inserted film
     * @param info
     * @throws java.sql.SQLException
     */
    private void insertDirectors(int filmId, DVDInfo info) throws SQLException {
        Set<String> directorsSet = info.getDirectors();
        String directorName;
        ResultSet result;
        Iterator iterator = directorsSet.iterator();
        int id;
        System.out.println("Inserting dir");
        if( directorsSet == null &&  directorsSet.isEmpty() ) {
            return;
        }
                
        while(iterator.hasNext()) {
            directorName = (String) iterator.next();
            this.findDirectorStmt.setString(1, directorName);
            result =  this.findDirectorStmt.executeQuery();
            id = result.getInt("id");
            
            if(id < 1) {
                logger.log(Level.INFO,"Insert director "+directorName);
                this.insertDirectorStmt.setString(1, directorName);
                this.insertDirectorStmt.execute();
                this.findDirectorStmt.setString(1, directorName);
                result =  this.findDirectorStmt.executeQuery();
                id = result.getInt(1);
            }
            result.close();
            logger.log(Level.INFO,"Director "+directorName+" has ID: "+id);
            this.insertFilm2DirectorStmt.setInt(1, filmId);
            this.insertFilm2DirectorStmt.setInt(2, id);
            this.insertFilm2DirectorStmt.execute();
        }
    }    
    
    /**
     * Function insert new actors to database and create connection to film
     * @param filmId unique ID of new inserted film
     * @param info
     * @throws java.sql.SQLException
     */
    private void insertActors(int filmId, DVDInfo info) throws SQLException {
        Set<String> actorsSet = info.getActors();
        String actorName;
        ResultSet result;
        Iterator iterator = actorsSet.iterator();
        int actorId = 0;
        
        if( actorsSet == null &&  actorsSet.isEmpty() ) {
            return;
        }
                
        while(iterator.hasNext()) {
            actorName = (String) iterator.next();
            System.out.println("Finding actor: "+actorName);
            this.findActorStmt.setString(1, actorName);
            result =  this.findActorStmt.executeQuery();
            actorId = result.getInt("id");
            
            if(actorId < 1) {
                logger.log(Level.INFO,"Insert actor "+actorName);
                this.insertActorStmt.setString(1, actorName);
                this.insertActorStmt.execute();
                this.findActorStmt.setString(1, actorName);
                result =  this.findActorStmt.executeQuery();
                actorId = result.getInt(1);
            }
            result.close();
            logger.log(Level.INFO,"Actor "+actorName+" has ID "+actorId);
            this.insertFilm2ActorStmt.setInt(1, filmId);
            this.insertFilm2ActorStmt.setInt(2, actorId);
            this.insertFilm2ActorStmt.execute();
        }
    }

    public void insertAdditionalInfo(int id, DVDInfo info) throws SQLException {
            this.insertAdditionalInfoStmt.setInt(1, id);
            this.insertAdditionalInfoStmt.setInt(2, info.getType().getInt());
            this.insertAdditionalInfoStmt.setString(3, info.getCompilation());
            this.insertAdditionalInfoStmt.setString(
                        4, info.getRentedToWho()
                );                
            this.insertAdditionalInfoStmt.setString(
                        5, info.getBorrowedFromWho()
                );
            this.insertAdditionalInfoStmt.setString(6, info.getInfo());
            this.insertAdditionalInfoStmt.execute();
    }
    /**
     * Function find maximum ID of record which match title 
     * use to determine inserted ID
     * @param title string specifies name of DVD
     * @return max ID which matching title 
     * @throws java.sql.SQLException when error in statement occurs
     * @throws java.lang.NullPointerException when title is null
     */
    private int getFilmID (String title) 
            throws SQLException, NullPointerException {
       if( title == null ) {
           throw new NullPointerException("title");
       }
       this.findFilmIdStmt.setString(1, title);
       ResultSet result = this.findFilmIdStmt.executeQuery();
       int resultId = result.getInt(1);
       result.close();
       return resultId;
    }
    
    public void deleteRecord(DVDInfo record) throws SQLException {
        if(record == null) {
            throw new NullPointerException("record");
        }
        if(record.getId() < 1) {
            throw new IllegalArgumentException("Record has no ID");
        }
        Statement stmt;
        stmt = this.connection.createStatement();
        stmt.execute("DELETE FROM film WHERE id="+record.getId());
        stmt.close();
        stmt = this.connection.createStatement();
        stmt.execute("DELETE FROM filmInfo WHERE id_film="+record.getId());
        stmt.close();
        
    }

    public void editRecord(DVDInfo record) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<DVDInfo> findEntries(DBCondition condition) throws SQLException {
        ResultSet result;
        ResultSet actorResult = null;
        ResultSet directorResult = null;
        ResultSet infoResult = null;
        List<DVDInfo> dvdSet = new ArrayList<DVDInfo>();
        DVDInfo dvdInfo = null;

        this.findFilmStmt.setString(1, condition.getString() );     
             
        result = this.findFilmStmt.executeQuery();
        
                
        while( result.next() ) {
            dvdInfo = new DVDInfo();
            dvdInfo.setId( result.getInt(1) );
            dvdInfo.setTitle( result.getString(2) );
            dvdInfo.setYearOfRelease( result.getInt(3));
            dvdInfo.setRating( result.getInt(4) );
            dvdInfo.setLength( result.getInt(5) );
            dvdInfo.setGenre( result.getString(6) );
            dvdInfo.setPosterUrl( result.getString(7) );
            
            dvdSet.add(dvdInfo);
        }   
        
        result.close();
        
        for(Iterator<DVDInfo> iterator = dvdSet.iterator(); 
            iterator.hasNext();) {
            dvdInfo = iterator.next();
            
            this.findInfoResult.setInt(1,dvdInfo.getId());
            infoResult = this.findInfoResult.executeQuery();

            while(infoResult.next()) {
                dvdInfo.setType( infoResult.getInt(1));
                dvdInfo.setCompilation( infoResult.getString(2));
                dvdInfo.setRentedToWho( infoResult.getString(3));
                dvdInfo.setBorrowedFromWho( infoResult.getString(4));
                dvdInfo.setInfo( infoResult.getString(5));
            }
            
            infoResult.close();

            this.findActorResult.setInt(1,dvdInfo.getId());
            actorResult = this.findActorResult.executeQuery();
//            
            while(actorResult.next()) {
                dvdInfo.addActor( actorResult.getString(1));
            }
            
            actorResult.close();
            
//            //Inserting director(s) in this movie
            this.findDirectorResult.setInt(1,dvdInfo.getId());
            directorResult = this.findDirectorResult.executeQuery();
            
            while(directorResult.next()) {
                dvdInfo.addDirector( directorResult.getString(1));
            }
            
            directorResult.close();
        }
        return dvdSet;
    }

    public void findEntries(DBCondition condition, JTable table) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    public boolean existEntry(int id) throws SQLException {       
        this.existFilmStmt.setInt(1, id);
        ResultSet result = this.existFilmStmt.executeQuery();
        boolean exist = result.next();
        result.close();
        return exist;
    }
      
    public static void main(String args[]) {
        try {
            DVDInfo info = new DVDInfo();
            info.setTitle("matrix");
            info.setYearOfRelease(1999);
            info.setGenre("sci-fi");
            info.setRating(10);
            info.addActor("Keanu Reeves");
            info.addActor("Laurence Fishburne");
            info.addActor("Carry-Anne Moss");
            info.addDirector("Larry Wachovski");
            info.addDirector("Andy Wachovski");

            
            DVDInfo info2 = new DVDInfo();
            info2.setTitle("Requiem for a dream");
            info2.setYearOfRelease(1999);
            info2.setGenre("drama");
            info2.setRating(10);
            info2.addActor("Jared Leto");
            info2.addActor("Jennifer Connely");
            info2.addDirector("Darren Arronofsky");
            
            DBSQLite db = new DBSQLite();
            db.openDb("/home/xatrix/java/spidex/database");
            DBCondition condition = new DBCondition(ConditionType.FILMNAME,"matrix");
            
            List<DVDInfo> dvdSet = db.findEntries(condition);
            db.close();
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DBSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

    public void close() throws SQLException {
        this.insertFilmStmt.close();
        this.insertActorStmt.close();
        this.insertDirectorStmt.close();
        this.insertFilm2ActorStmt.close();
        this.insertFilm2DirectorStmt.close();
        this.insertAdditionalInfoStmt.close();
        this.updateFilmStmt.close();
        this.updateAdditionalInfoStmt.close();
        this.findFilmIdStmt.close();
        this.findFilmStmt.close();
        this.findActorStmt.close();
        this.findDirectorStmt.close();
        this.findInfoResult.close();
        this.findActorResult.close();
        this.findDirectorResult.close();
        this.connection.setAutoCommit(false);
        this.connection.rollback();
        this.connection.close();
    }
}
